VERSION 5.00
Object = "{8E27C92E-1264-101C-8A2F-040224009C02}#7.0#0"; "MSCAL.OCX"
Begin VB.Form frmReminders 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Assessment Notes"
   ClientHeight    =   3285
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   6375
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3285
   ScaleWidth      =   6375
   ShowInTaskbar   =   0   'False
   StartUpPosition =   3  'Windows Default
   Begin MSACAL.Calendar calNOT_COMPLETED_DATE 
      Height          =   2295
      Left            =   3960
      TabIndex        =   17
      Top             =   960
      Width           =   2415
      _Version        =   524288
      _ExtentX        =   4260
      _ExtentY        =   4048
      _StockProps     =   1
      BackColor       =   -2147483633
      Year            =   2006
      Month           =   1
      Day             =   22
      DayLength       =   2
      MonthLength     =   2
      DayFontColor    =   0
      FirstDay        =   7
      GridCellEffect  =   1
      GridFontColor   =   10485760
      GridLinesColor  =   -2147483632
      ShowDateSelectors=   -1  'True
      ShowDays        =   -1  'True
      ShowHorizontalGrid=   -1  'True
      ShowTitle       =   0   'False
      ShowVerticalGrid=   -1  'True
      TitleFontColor  =   10485760
      ValueIsNull     =   0   'False
      BeginProperty DayFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty GridFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty TitleFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin MSACAL.Calendar calNOT_NOTIFY_FROM 
      Height          =   2295
      Left            =   2280
      TabIndex        =   16
      Top             =   960
      Width           =   2415
      _Version        =   524288
      _ExtentX        =   4260
      _ExtentY        =   4048
      _StockProps     =   1
      BackColor       =   -2147483633
      Year            =   2006
      Month           =   1
      Day             =   22
      DayLength       =   2
      MonthLength     =   1
      DayFontColor    =   0
      FirstDay        =   7
      GridCellEffect  =   1
      GridFontColor   =   10485760
      GridLinesColor  =   -2147483632
      ShowDateSelectors=   -1  'True
      ShowDays        =   -1  'True
      ShowHorizontalGrid=   -1  'True
      ShowTitle       =   0   'False
      ShowVerticalGrid=   -1  'True
      TitleFontColor  =   10485760
      ValueIsNull     =   0   'False
      BeginProperty DayFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty GridFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty TitleFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin MSACAL.Calendar calNOT_CREATED 
      Height          =   2175
      Left            =   2280
      TabIndex        =   15
      Top             =   600
      Width           =   2415
      _Version        =   524288
      _ExtentX        =   4260
      _ExtentY        =   3836
      _StockProps     =   1
      BackColor       =   -2147483633
      Year            =   2006
      Month           =   1
      Day             =   22
      DayLength       =   2
      MonthLength     =   2
      DayFontColor    =   0
      FirstDay        =   7
      GridCellEffect  =   1
      GridFontColor   =   10485760
      GridLinesColor  =   -2147483632
      ShowDateSelectors=   -1  'True
      ShowDays        =   -1  'True
      ShowHorizontalGrid=   -1  'True
      ShowTitle       =   0   'False
      ShowVerticalGrid=   -1  'True
      TitleFontColor  =   10485760
      ValueIsNull     =   0   'False
      BeginProperty DayFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty GridFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty TitleFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   12
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin VB.CommandButton cmdAdd 
      Caption         =   "&Add"
      Height          =   255
      Left            =   3240
      TabIndex        =   14
      Top             =   120
      Width           =   735
   End
   Begin VB.CommandButton cmdDown 
      Caption         =   "Dn"
      Height          =   375
      Left            =   840
      TabIndex        =   13
      Top             =   2760
      Visible         =   0   'False
      Width           =   615
   End
   Begin VB.CommandButton cmdUp 
      Caption         =   "Up"
      Height          =   375
      Left            =   120
      TabIndex        =   12
      Top             =   2760
      Visible         =   0   'False
      Width           =   615
   End
   Begin VB.CommandButton cmdDelete 
      Caption         =   "&Delete"
      Height          =   375
      Left            =   3960
      TabIndex        =   3
      Top             =   2760
      Width           =   1095
   End
   Begin VB.CommandButton cmdUpdate 
      Caption         =   "&Update"
      Height          =   375
      Left            =   5160
      TabIndex        =   11
      Top             =   2760
      Width           =   1095
   End
   Begin VB.TextBox txtNOT_COMPLETED_DATE 
      Height          =   285
      Left            =   4320
      TabIndex        =   8
      Text            =   "NOT_COMPLETED_DATE"
      Top             =   960
      Width           =   1935
   End
   Begin VB.CheckBox chkNOT_COMPLETED 
      Alignment       =   1  'Right Justify
      Caption         =   "Complete"
      Height          =   255
      Left            =   5040
      TabIndex        =   7
      Top             =   600
      Width           =   1215
   End
   Begin VB.TextBox txtNOT_NOTIFY_FROM 
      Height          =   285
      Left            =   2280
      TabIndex        =   6
      Text            =   "NOT_NOTIFY_FROM"
      Top             =   960
      Width           =   1935
   End
   Begin VB.TextBox txtNOT_CREATED 
      Height          =   285
      Left            =   2280
      TabIndex        =   5
      Text            =   "NOT_CREATED"
      Top             =   600
      Width           =   1935
   End
   Begin VB.TextBox txtNOT_NOTE 
      Height          =   1335
      Left            =   1560
      MultiLine       =   -1  'True
      TabIndex        =   4
      Text            =   "frmReminders.frx":0000
      Top             =   1320
      Width           =   4695
   End
   Begin VB.ComboBox cmbASS_INVOICE_NO 
      Height          =   315
      Left            =   1200
      TabIndex        =   2
      Text            =   "ASS_INVOICE_NO"
      Top             =   120
      Width           =   1935
   End
   Begin VB.ListBox lstNOT_SEQUENCE 
      Height          =   2010
      ItemData        =   "frmReminders.frx":0009
      Left            =   120
      List            =   "frmReminders.frx":000B
      TabIndex        =   0
      Top             =   600
      Width           =   1335
   End
   Begin VB.Label Label3 
      Alignment       =   1  'Right Justify
      Caption         =   "Start"
      Height          =   255
      Left            =   1560
      TabIndex        =   10
      Top             =   960
      Width           =   615
   End
   Begin VB.Label Label2 
      Alignment       =   1  'Right Justify
      Caption         =   "Created"
      Height          =   255
      Left            =   1560
      TabIndex        =   9
      Top             =   600
      Width           =   615
   End
   Begin VB.Label Label1 
      Alignment       =   1  'Right Justify
      Caption         =   "Assessment"
      Height          =   255
      Left            =   120
      TabIndex        =   1
      Top             =   120
      Width           =   975
   End
   Begin VB.Line Line1 
      X1              =   120
      X2              =   6240
      Y1              =   480
      Y2              =   480
   End
End
Attribute VB_Name = "frmReminders"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
'==================================================================================
'Copyright (c) 2003, Andrew Jeremy Gargan (ajg@osite.co.za), Terence Leslie Van Zyl (tvz@osite.co.za)
'
'All rights reserved.
'
'Redistribution and use in source and binary forms, with or without modification,
'are permitted provided that the following conditions are met:
'
'Redistributions of source code must retain the above copyright notice, this list
'of conditions and the following disclaimer.
'
'Redistributions in binary form must reproduce the above copyright notice, this
'list of conditions and the following disclaimer in the documentation and/or other
'materials provided with the distribution.
'
'Neither the name of the <ORGANIZATION> nor the names of its contributors may be
'used to endorse or promote products derived from this software without specific
'prior written permission.
'
'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
'ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
'WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
'IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
'INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
'BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
'DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
'LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
'OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
'OF THE POSSIBILITY OF SUCH DAMAGE.
'===================================================================================

Public mvarAction As String
Public mvarASS_KEY As String
Public mvarNOT_KEY As String
Public mvarNOT_SEQUENCE As String

Private Sub able_form(enable As Boolean)
    Dim lvarControl As Control
    For Each lvarControl In Controls
        If Not UCase(TypeName(lvarControl)) Like "CAL*" And Not UCase(TypeName(lvarControl)) Like "LINE*" Then
            'MsgBox TypeName(lvarControl)
            lvarControl.Enabled = enable
        End If
    Next
End Sub

Private Sub build_assessment_list()
    Dim lvarRS As DAO.Recordset
    Dim lvarQuery As String
    Dim lvarPos As Long
    'Get a List of all the Assessments With REMINDERs
        
    'Clear List
    cmbASS_INVOICE_NO.Clear
    
    lvarQuery = "SELECT DISTINCT REMINDER.ASS_KEY,ASS_RE_ASSESSMENT,ASS_INVOICE_NO FROM REMINDER INNER JOIN ASSESSMENT ON REMINDER.ASS_KEY = ASSESSMENT.ASS_KEY ORDER BY ASS_INVOICE_NO"
    Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)
    
    If lvarRS.EOF Then
        'msgbox "Sorry there are no REMINDERs for any Assessments!"
        'Disable form
        able_form False
    Else
        able_form True
        lvarPos = 0
        Do While Not lvarRS.EOF
            cmbASS_INVOICE_NO.AddItem IIf(IsNull(lvarRS("ASS_RE_ASSESSMENT")), "RE-", "") & lvarRS("ASS_INVOICE_NO")
            cmbASS_INVOICE_NO.ItemData(cmbASS_INVOICE_NO.NewIndex) = lvarRS("ASS_KEY")
            If mvarASS_KEY = CStr(lvarRS("ASS_KEY")) Then
                lvarPos = cmbASS_INVOICE_NO.NewIndex
            End If
            lvarRS.MoveNext
        Loop
        cmbASS_INVOICE_NO.ListIndex = lvarPos
    End If
End Sub

Private Sub list_REMINDERs()
    Dim lvarRS As DAO.Recordset
    Dim lvarQuery As String
    Dim lvarPos As Long
    
    'Clear List
    lstNOT_SEQUENCE.Clear
    
    lvarQuery = "SELECT * FROM REMINDER WHERE ASS_KEY=" & mvarASS_KEY & " ORDER BY NOT_SEQUENCE, NOT_NOTIFY_FROM "
    Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)
    
    If lvarRS.EOF Then
        'msgbox "Sorry there are no REMINDERs for the selected Assessment!"
    Else
        lvarPos = 0
        Do While Not lvarRS.EOF
            lstNOT_SEQUENCE.AddItem lvarRS("NOT_SEQUENCE") & " - " & Format(lvarRS("NOT_NOTIFY_FROM"), "dd-mmm-yyyy")
            lstNOT_SEQUENCE.ItemData(lstNOT_SEQUENCE.NewIndex) = lvarRS("NOT_KEY")
            If mvarNOT_KEY = CStr(lvarRS("NOT_KEY")) Then
                lvarPos = lstNOT_SEQUENCE.NewIndex
            End If
            lvarRS.MoveNext
        Loop
        lstNOT_SEQUENCE.ListIndex = lvarPos
    End If
End Sub


Private Sub cmbASS_INVOICE_NO_Click()
    'set mvarASS_KEY
    mvarASS_KEY = CStr(cmbASS_INVOICE_NO.ItemData(cmbASS_INVOICE_NO.ListIndex))
    
    'Build list of REMINDERs
    list_REMINDERs
    
    'Enable form
    
End Sub

Private Sub add_REMINDER()
    Dim lvarSequence As String
    Dim lvarQuery As String
    
    lvarQuery = "SELECT (count(*)+1) AS SEQUENCE FROM REMINDER WHERE ASS_KEY=" & mvarASS_KEY
    lvarSequence = CStr(gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)("SEQUENCE"))
    
    lvarQuery = "INSERT INTO REMINDER (NOT_SEQUENCE,ASS_KEY,NOT_CREATED) VALUES (""" & lvarSequence & """," & mvarASS_KEY & ",#" & Format(VBA.Date, "dd-mmm-yyyy") & "# );"
    
    On Error GoTo errorhandler
    'create record
    DBEngine.BeginTrans
    gvarDatabase.Execute lvarQuery, dbFailOnError
    mvarNOT_KEY = CStr(gvarDatabase.OpenRecordset("SELECT NOT_KEY FROM REMINDER WHERE ASS_KEY = " & mvarASS_KEY & " AND NOT_SEQUENCE = " & lvarSequence, dbOpenForwardOnly)("NOT_KEY"))
    'REMINDER the key
    DBEngine.CommitTrans
    On Error GoTo 0
    'Build the list
    mvarAction = ""
    build_assessment_list
    Exit Sub
errorhandler:
    DBEngine.Rollback
    MsgBox DBEngine.Errors(0).Number & " - " & DBEngine.Errors(0).Description
    On Error GoTo 0
End Sub

Private Sub Fill_Form()
    If mvarAction = "ADD" Then
        add_REMINDER
    Else
        build_assessment_list
    End If
End Sub

Private Sub cmdAdd_Click()
    mvarASS_KEY = cmbASS_INVOICE_NO.ItemData(cmbASS_INVOICE_NO.ListIndex)
    mvarAction = "ADD"
    
    clear_form
    Fill_Form
    
End Sub

Private Sub delete()
    Dim lvarQuery As String
    
    lvarQuery = "DELETE FROM REMINDER WHERE NOT_KEY=" & mvarNOT_KEY
    On Error GoTo errorhandler
    gvarDatabase.Execute lvarQuery, dbFailOnError
    mvarNOT_KEY = ""
    mvarNOT_SEQUENCE = ""
    MsgBox "Reminder Deleted!", vbOKOnly, "Success!"
    Exit Sub
errorhandler:
    On Error GoTo 0
End Sub

Private Sub cmdDelete_Click()
    delete
    clear_form
    Fill_Form
End Sub

Private Sub cmdUpdate_Click()
    update
End Sub

Private Sub Form_Load()
    clear_form
    Fill_Form
End Sub

Private Sub clear_form()
    Dim lvarControl As Control
    
    For Each lvarControl In Controls
        If TypeName(lvarControl) = "TextBox" And UCase(lvarControl.Name) Like "???NOT_*" Then
            lvarControl.Text = ""
        ElseIf TypeName(lvarControl) = "CheckBox" And UCase(lvarControl.Name) Like "???NOT_*" Then
            lvarControl.Value = 0
        ElseIf TypeName(lvarControl) = "ComboBox" And UCase(lvarControl.Name) Like "???FOR_*" Then
            'TODO:look at tag for table and field to display
            'build list KEY FROM Control Name
            'if selected select
        ElseIf TypeName(lvarControl) = "Calendar" Then
            lvarControl.Visible = False
            lvarControl.Value = VBA.Date
        ElseIf TypeName(lvarControl) = "CommandButton" Then
            'do nothing
        End If
    Next
End Sub

Private Sub update()
    Dim lvarSequence As String
    Dim lvarQuery As String
    
    '"NOT_SEQUENCE=" & mvarNOT_SEQUENCE & "," & _

    lvarQuery = "UPDATE REMINDER SET " & _
                "NOT_CREATED= " & IIf(txtNOT_CREATED <> "", "#" & txtNOT_CREATED & "#", "NULL") & "," & _
                "NOT_NOTE=""" & txtNOT_NOTE & """," & _
                "ASS_KEY=" & mvarASS_KEY & "," & _
                "NOT_COMPLETED=" & IIf(chkNOT_COMPLETED.Value = 0, "false", "true") & "," & _
                "NOT_NOTIFY_FROM= " & IIf(txtNOT_NOTIFY_FROM <> "", "#" & txtNOT_NOTIFY_FROM & "#", "NULL") & "," & _
                "NOT_COMPLETED_DATE= " & IIf(txtNOT_COMPLETED_DATE <> "", "#" & txtNOT_COMPLETED_DATE & "# ", "NULL ") & _
                "WHERE NOT_KEY=" & mvarNOT_KEY
    
    lvarSequence = mvarNOT_SEQUENCE
    On Error GoTo errorhandler
    'create record
    DBEngine.BeginTrans
    gvarDatabase.Execute lvarQuery
    mvarNOT_KEY = CStr(gvarDatabase.OpenRecordset("SELECT NOT_KEY FROM REMINDER WHERE ASS_KEY = " & mvarASS_KEY & " AND NOT_SEQUENCE = " & lvarSequence, dbOpenForwardOnly)("NOT_KEY"))
    'REMINDER the key
    DBEngine.CommitTrans
    On Error GoTo 0
    'Build the list
    build_assessment_list
    Exit Sub
errorhandler:
    DBEngine.Rollback
    'InputBox "AAA", , lvarQuery
    MsgBox DBEngine.Errors(0).Number & " - " & DBEngine.Errors(0).Description
    On Error GoTo 0
End Sub

Private Sub fill_REMINDER()
    Dim lvarRS As DAO.Recordset
    Dim lvarQuery As String
    
    lvarQuery = "SELECT * FROM REMINDER WHERE NOT_KEY =" & mvarNOT_KEY
    Set lvarRS = gvarDatabase.OpenRecordset(lvarQuery, dbOpenForwardOnly)
    If Not lvarRS.EOF Then
        txtNOT_NOTE = IIf(IsNull(lvarRS("NOT_NOTE")), "", lvarRS("NOT_NOTE"))
    
        txtNOT_CREATED = IIf(IsNull(lvarRS("NOT_CREATED")), "", lvarRS("NOT_CREATED"))
        calNOT_CREATED = IIf(IsNull(lvarRS("NOT_CREATED")), VBA.Date, lvarRS("NOT_CREATED"))
        
        txtNOT_NOTIFY_FROM = IIf(IsNull(lvarRS("NOT_NOTIFY_FROM")), "", lvarRS("NOT_NOTIFY_FROM"))
        calNOT_NOTIFY_FROM = IIf(IsNull(lvarRS("NOT_NOTIFY_FROM")), VBA.Date, lvarRS("NOT_NOTIFY_FROM"))
        
        txtNOT_COMPLETED_DATE = IIf(IsNull(lvarRS("NOT_COMPLETED_DATE")), "", lvarRS("NOT_COMPLETED_DATE"))
        calNOT_COMPLETED_DATE = IIf(IsNull(lvarRS("NOT_COMPLETED_DATE")), VBA.Date, lvarRS("NOT_COMPLETED_DATE"))
        
        chkNOT_COMPLETED = IIf(IsNull(lvarRS("NOT_COMPLETED")), 0, IIf(lvarRS("NOT_COMPLETED") = True, 1, 0))
        mvarNOT_SEQUENCE = lvarRS("NOT_SEQUENCE")
    End If
    able_form True
End Sub

Private Sub lstNOT_SEQUENCE_Click()
    'Set Key
    mvarNOT_KEY = lstNOT_SEQUENCE.ItemData(lstNOT_SEQUENCE.ListIndex)
    
    'Bring Details
    fill_REMINDER
End Sub

Private Sub txtNOT_CREATED_Click()
    calNOT_CREATED.Visible = True
End Sub

Private Sub calNOT_CREATED_dblClick()
    calNOT_CREATED.Visible = False
    txtNOT_NOTE.SetFocus
End Sub

Private Sub calNOT_CREATED_lostFocus()
    calNOT_CREATED.Visible = False
    txtNOT_CREATED = calNOT_CREATED.Value
End Sub

Private Sub txtNOT_NOTIFY_FROM_Click()
    calNOT_NOTIFY_FROM.Visible = True
End Sub

Private Sub calNOT_NOTIFY_FROM_dblClick()
    calNOT_NOTIFY_FROM.Visible = False
    txtNOT_NOTE.SetFocus
End Sub

Private Sub calNOT_NOTIFY_FROM_lostFocus()
    calNOT_NOTIFY_FROM.Visible = False
    txtNOT_NOTIFY_FROM = calNOT_NOTIFY_FROM.Value
End Sub

Private Sub txtNOT_COMPLETED_DATE_Click()
    calNOT_COMPLETED_DATE.Visible = True
End Sub

Private Sub calNOT_COMPLETED_DATE_dblClick()
    calNOT_COMPLETED_DATE.Visible = False
    txtNOT_NOTE.SetFocus
End Sub

Private Sub calNOT_COMPLETED_DATE_lostFocus()
    calNOT_COMPLETED_DATE.Visible = False
    txtNOT_COMPLETED_DATE = calNOT_COMPLETED_DATE.Value
End Sub

